<?php
require_once("include/Gruper/AccountData.php");

class Research{
    protected $db;
    protected $from;
    protected $to;
    protected $userId = null;
    public $reportUserSummary;
    const CONSULTATN_ROLE_ID = "6e9e9053-007d-cdc1-ff24-4bc43bc4fe84";
    
    public function __construct($from, $to){
        $this->db = $GLOBALS['db'];
        $this->from = $from;
        $this->to = $to;
        $this->reportUserSummary = array(
            'supportedAccountCnt' => 0,
            'dealAccountCnt' => 0,
            'research' => 0,
            'own' => 0,
            'accCnt' => 0,
        );
    }

    public function createResearchReport($from, $to){
        $report = array();

        $usersOpp = $this->getUsersOpp();
        $usersActivity = $this->getUsersActivity();
        $accountsUsersArr = $this->getAssignedAccountsToUsers();
        $users = $this->getConsultantUsers();
        
        if(!empty($users)){
            foreach($users as $user){
                $assignedAccounts = 0;
                $callsOrMeetingsNo = 0;
                $oppNo = 0;
                foreach($accountsUsersArr as $accountId => $assignedUsersIds){
                    if(in_array($user['userId'], $assignedUsersIds)){
                        ++$assignedAccounts;
                    }
                }
                if(isset($usersActivity[$user['userId']])){
                    $callsOrMeetingsNo = $usersActivity[$user['userId']];
                }
                if(isset($usersOpp[$user['userId']])){
                    $oppNo = $usersOpp[$user['userId']];
                }

                $report[] = array(
                    'userName' => $user['userName'],
                    'userId' => $user['userId'],
                    'assignedAccounts' => $assignedAccounts,
                    'callsOrMeetingsNo' => $callsOrMeetingsNo,
                    'callsOrMeetingsPercent' => $assignedAccounts != 0? $callsOrMeetingsNo/$assignedAccounts * 100 : $assignedAccounts,
                    'oppNo' => $oppNo,
                    'oppPercent' => $assignedAccounts != 0? $oppNo/$assignedAccounts * 100 : $assignedAccounts,
                );
            }
        }
        return $report;

    }
    public function getUsersOpp(){
        $sql ="SELECT count(*) as opp, a.id as account_id, u.id as userId
                        FROM opportunities o
                        INNER JOIN users u ON o.created_by = u.id
                        INNER JOIN accounts_opportunities ao ON o.id = ao.opportunity_id
                        INNER JOIN accounts a ON a.id = ao.account_id
                        WHERE o.date_entered >= '$this->from 00:00:00' AND o.date_entered <= '$this->to 23:59:59'
                        AND o.sales_stage = 'Closed Won'
                        AND o.deleted = 0
                        AND a.deleted = 0
                        AND ao.deleted = 0";

        if($this->userId != null){
            $sql .= " AND u.id = '$this->userId'";
        }else{
            $sql .= " AND (a.source_entered = 'research' OR a.source_entered = 'research_finished')";
        }
        $sql .= " GROUP BY a.id, u.id";

        $usersAccounts = array();
        $usersActivity = array();
        $result = $this->db->query($sql);
        while($row = $this->db->fetchByAssoc($result) ){
            if(!isset($usersActivity[$row['userId']])){
                $usersActivity[$row['userId']] = 1;
                $usersAccounts[$row['userId']] = array($row['account_id']);
            }else{
                if(!in_array($row['account_id'],$usersAccounts[$row['userId']])){
                    $usersActivity[$row['userId']] += 1;
                    $usersAccounts[$row['userId']][] = $row['account_id'];
                }
            }
            //$usersCalls[$row['userId']] = $row['calls'];
        }

        if($this->userId != null){
            return $usersAccounts;
        }
        return $usersActivity;
    }
    public function getUsersActivity(){
        $sqlCall = "SELECT count(*) as no, a.id as account_id, u.id as userId
                FROM `calls` c
                INNER JOIN users u ON c.created_by = u.id
                INNER JOIN accounts_contacts ac ON c.parent_id = ac.contact_id
                INNER JOIN accounts a ON a.id = ac.account_id
                WHERE c.date_entered >= '$this->from 00:00:00' AND c.date_entered <= '$this->to 23:59:59'
                AND c.deleted=0
                AND a.deleted = 0
                AND ac.deleted = 0";
        if($this->userId != null){
            $sqlCall .= " AND u.id = '$this->userId'";
        }else{
            $sqlCall .= " AND (a.source_entered = 'research' OR a.source_entered = 'research_finished')";
        }
        $sqlCall .= " GROUP BY a.id, u.id";

        $sqlMeeting = "SELECT count(*) as no, a.id as account_id, u.id as userId
                        FROM meetings m
                        INNER JOIN users u ON m.created_by = u.id
                        INNER JOIN accounts_contacts ac ON m.parent_id = ac.contact_id
                        INNER JOIN accounts a ON a.id = ac.account_id
                        WHERE m.date_entered >= '$this->from 00:00:00' AND m.date_entered <= '$this->to 23:59:59'
                        AND m.deleted = 0
                        AND a.deleted = 0
                        AND ac.deleted = 0";
        if($this->userId != null){
            $sqlMeeting .= " AND u.id = '$this->userId'";
        }else{
           $sqlMeeting .= "AND (a.source_entered = 'research' OR a.source_entered = 'research_finished')";
        }
        $sqlMeeting .= " GROUP BY a.id, u.id";
        $sql = $sqlCall." UNION ".$sqlMeeting;

        $usersAccounts = array();
        $usersActivity = array();
        $result = $this->db->query($sql);
        while($row = $this->db->fetchByAssoc($result) ){
            if(!isset($usersActivity[$row['userId']])){
                $usersActivity[$row['userId']] = 1;
                $usersAccounts[$row['userId']] = array($row['account_id']);
            }else{
                if(!in_array($row['account_id'],$usersAccounts[$row['userId']])){
                    $usersActivity[$row['userId']] += 1;
                    $usersAccounts[$row['userId']][] = $row['account_id'];
                }
            }
            //$usersCalls[$row['userId']] = $row['calls'];
        }

        if($this->userId != null){
            return $usersAccounts;
        }
        return $usersActivity;
    }

    public function getAssignedAccountsToUsers(){
        $sql = "SELECT DISTINCT a.id as account_id, DATE(a.date_entered)as acc_entered, DATE(aa.date_created) as acc_changed,a.assigned_user_id, aa.before_value_string, aa.after_value_string
                FROM accounts a
                LEFT JOIN accounts_audit aa ON a.id = aa.parent_id AND DATE(aa.date_created) BETWEEN '$this->from' AND '$this->to' AND aa.field_name = 'assigned_user_id'
                WHERE DATE(a.date_entered) BETWEEN '$this->from' AND '$this->to' 
                AND a.deleted = 0";
        if($this->userId != null){
            $sql .= " AND (a.assigned_user_id ='$this->userId' OR aa.before_value_string = '$this->userId' OR aa.after_value_string  = '$this->userId' )";
        }else{
            $sql .= " AND (a.source_entered = 'research' OR a.source_entered = 'research_finished')";
        }
        $sql .= " ORDER BY a.id,a.date_entered, aa.date_created";
        //echo $sql;
        $accountsUsersArr = array();
        $accountsAudit = $this->getAllRows($sql);
        $accountsAuditCnt = count($accountsAudit);
        for($i=0; $i<$accountsAuditCnt; $i++){
            if(!isset($accountsUsersArr[$accountsAudit[$i]['account_id']])){
                $accountsUsersArr[$accountsAudit[$i]['account_id']] = array($accountsAudit[$i]['assigned_user_id']);
            }
            if(!in_array($accountsAudit[$i]['before_value_string'], $accountsUsersArr[$accountsAudit[$i]['account_id']]) && !empty($accountsAudit[$i]['before_value_string'])){
                $accountsUsersArr[$accountsAudit[$i]['account_id']][] = $accountsAudit[$i]['before_value_string'];
            }
            if(!in_array($accountsAudit[$i]['after_value_string'], $accountsUsersArr[$accountsAudit[$i]['account_id']]) && !empty($accountsAudit[$i]['after_value_string'])){
                $accountsUsersArr[$accountsAudit[$i]['account_id']][] = $accountsAudit[$i]['after_value_string'];
            }

        }

        return $accountsUsersArr;
    }

    public function getConsultantUsers(){
        $sql = "SELECT CONCAT(u.last_name, ' ' ,IFNULL(u.first_name, ''), ' / ' ,IFNULL(u.user_name, '')) as userName, u.id as userId
            FROM users u
            INNER JOIN acl_roles_users aru ON aru.user_id = u.id
            WHERE aru.role_id = '".self::CONSULTATN_ROLE_ID."' AND aru.deleted = 0 AND u.status = 'Active'
            ORDER BY u.user_name, u.last_name, u.first_name ";
        return $this->getAllRows($sql);
    }

    protected function getAllRows($sql){
        $rows = array();
        $result = $this->db->query($sql);
        while($row = $this->db->fetchByAssoc($result) ){
            $rows[] = $row;
        }

        return $rows;
    }

    public function hasUserAccessToResearch($userId){
        if( isset($_SESSION['ACL'][$userId]['research_access'])){
            if(!is_null($_SESSION['ACL'][$userId]['research_access'])){
               return $_SESSION['ACL'][$userId]['research_access'];
            }
        }
        $userRolesArr = ACLRole::getUserRoles($userId, false);

        if(!empty($userRolesArr)){
            foreach($userRolesArr as $userRole){
                if($userRole->name == 'Research' || $userRole->id == '9d83f93f-beb7-11e0-babd-d8b606f9d028'){
                    $_SESSION['ACL'][$userId]['research_access'] = true;
                    return true;
                }
            }
        }

        $_SESSION['ACL'][$userId]['research_access'] = false;
        return false;
    }

    public function createUserReport(){
        $accountData = new AccountData();

        $report = array();
        $accountUsers = $this->getAssignedAccountsToUsers();
        $accountsId = array_keys($accountUsers);
        $accounts = $this->getAccountsData($accountsId);
        $accountData->setAccounts($accountsId);

        if(!empty($accounts)){
            $supportedAcc = $this->getUsersActivity();
            $userOpp = $this->getUsersOpp();

            $supportedAccountCnt = 0;
            $dealAccountCnt = 0;
            $research = 0;
            $own = 0;
            foreach($accounts as $acc){
                //$acc['description'] .= $accountData->getLastTopic($acc['id']);
                $supportedAccount = false;
                $dealAccount = false;

                if(isset($supportedAcc[$this->userId])){
                    if(in_array($acc['id'], $supportedAcc[$this->userId])){
                        $supportedAccount = true;
                        ++$supportedAccountCnt;
                    }
                }
                if(isset($userOpp[$this->userId])){
                    if(in_array($acc['id'], $userOpp[$this->userId])){
                        $dealAccount = true;
                        ++$dealAccountCnt;
                    }
                }
                if(empty($acc['source_entered'])){
                    ++$own;
                }else{
                    ++$research;
                }
                $report[] = array(
                    'dateEntered' => $acc['date_created'],
                    'name' => $acc['name'],
                    'product' => $acc['gruper_partner_rank_spec_c'],
                    'byResearchEntered' => !empty($acc['source_entered']),
                    'supportedAccount' => $supportedAccount,
                    'dealAccount' => $dealAccount,
                    'desc' => $acc['description'],
                    'accData' => $accountData->getLastTopic($acc['id'])
                );
            }
            $this->reportUserSummary['supportedAccountCnt'] = $supportedAccountCnt;
            $this->reportUserSummary['dealAccountCnt'] = $dealAccountCnt;
            $this->reportUserSummary['research'] = $research;
            $this->reportUserSummary['own'] = $own;
            $this->reportUserSummary['accCnt'] = $own + $research;

        }
        return $report;

    }

    public function setReportForUser($userId){
        $this->userId = $userId;
    }

    protected function getAccountsData(array $accIds){
        $accIds = '"'.implode('", "', $accIds).'"';
        $sql = "SELECT *, DATE(a.date_entered) as date_created FROM accounts a
            INNER JOIN accounts_cstm ac ON a.id = ac.id_c
            WHERE a.id IN (".$accIds .")";

        return $this->getAllRows($sql);
    }
}